﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;
using RedemptoristRetreatCenter.HelperUtility;

namespace RedemptoristRetreatCenter.DatabaseUtility
{
    /// <summary>
    /// The user account utility class connects with the the RedemptoristRetreatCenter Database and uses SQL to grab information pertaining to registered users
    /// </summary>
    public class UserAccountUtility
    {
       private SqlConnection rrcDbConn;

       public UserAccountUtility()
        {
            rrcDbConn = new SqlConnection();
            rrcDbConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        private void openConnection()
            {
                try
                {
                    rrcDbConn.Open();
                    Console.WriteLine(Environment.NewLine + "rrcDbConn.State: " + rrcDbConn.State.ToString());

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error opening connecdtion: " + ex.ToString());
                }
            }

        public void closeConnection()
        {
            rrcDbConn.Close();
            Console.WriteLine("rrcDbConn.State: " + rrcDbConn.State.ToString());
        }

        /// <summary>
        /// Determining whether a username (user) already exists within the database
        /// </summary>
        /// <param name="username"></param>
        /// <returns>bool</returns>
        public bool userExists(string username)
        {
            // -- Creating SQL to determine whether "username" exists within the database --
            SqlCommand sqlCmd = new SqlCommand(String.Format("SELECT COUNT(1) AS userExists FROM UserAccount WHERE UPPER(username) = UPPER('{0}')",username), rrcDbConn);

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader userAccountReader = null;
            userAccountReader = sqlCmd.ExecuteReader();
            int rowCount = 0;

            // -- executing SQL to determine if any rows within the database exist with the corresponding username --
            if (userAccountReader.Read())
            {
                rowCount = (int)userAccountReader["userExists"];
            }

            closeConnection();
            return (rowCount > 0);
        }

        /// <summary>
        /// Selecting a user Id based on the user's first name, last name, and email address 
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <param name="emailAddress"></param>
        /// <returns>int</returns>
        public int getUserId(String firstName, String lastName, String emailAddress)
        {
            int userId = -1;

            // -- Creating SQL to find the userId based on the user's first name, last name, and email address --
            StringBuilder sqlText = new StringBuilder("SELECT userId ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE firstName='");
            sqlText.Append(firstName);
            sqlText.Append("' AND lastName='");
            sqlText.Append(lastName);
            sqlText.Append("' AND emailAddress='");
            sqlText.Append(emailAddress);
            sqlText.Append("';");

            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader userAccountReader = null;
            userAccountReader = sqlCmd.ExecuteReader();

            // -- executing SQL to find the correct userId --
            while (userAccountReader.Read())
            {
                userId = (int)userAccountReader["userId"];
            }

            closeConnection();

            return userId;
        }

        /// <summary>
        /// validate username and password against database
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public UserAccountInfo validateCredentials(string username, string password)
        {
            SqlCommand sqlCmd = new SqlCommand(String.Format("SELECT userId FROM UserAccount WHERE UPPER(username)=UPPER('{0}') and password='{1}' AND activeData=1 ORDER BY modifiedTimestamp desc;", username, password), rrcDbConn);
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }
            
            SqlDataReader userAccountReader = null;
            userAccountReader = sqlCmd.ExecuteReader();
            int userId = 0;

            if (userAccountReader.Read())
            {
                userId = (int)userAccountReader["userId"];
            }

            closeConnection();
            return (this.getUserAccountInfoById(userId));
        }

        /// <summary>
        /// return list of users with administrator privs
        /// </summary>
        /// <returns></returns>
        public List<UserAccountInfo> getAdminList()
        {
            StringBuilder sqlText = new StringBuilder("SELECT userId,username,password,administrator,staff,volunteer,firstName,lastName,addressLine1,addressLine2,");
            sqlText.Append("city,state,zipCode,homePhone,cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE administrator=1 AND activeData=1 ");
            sqlText.Append("ORDER BY lastName;");
            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            return retrieveUserAccountData(sqlCmd);
        }
        /// <summary>
        /// return list of staff members
        /// </summary>
        /// <returns></returns>
        public List<UserAccountInfo> getStaffList()
        {
            StringBuilder sqlText = new StringBuilder("SELECT userId,username,password,administrator,staff,volunteer,firstName,lastName,addressLine1,addressLine2,");
            sqlText.Append("city,state,zipCode,homePhone,cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE staff=1 AND activeData=1 ");
            sqlText.Append("ORDER BY lastName;");
            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            return retrieveUserAccountData(sqlCmd);
        }

        /// <summary>
        /// return list of volunteers
        /// </summary>
        /// <returns></returns>
        public List<UserAccountInfo> getVolunteerList()
        {
            StringBuilder sqlText = new StringBuilder("SELECT userId,username,password,administrator,staff,volunteer,firstName,lastName,addressLine1,addressLine2,");
            sqlText.Append("city,state,zipCode,homePhone,cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE volunteer=1 AND activeData=1 ");
            sqlText.Append("ORDER BY lastName;");
            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            return retrieveUserAccountData(sqlCmd);
        }

        /// <summary>
        /// return list of all users including admin, staff, volunteer, and basic website visitor
        /// </summary>
        /// <returns></returns>
        public List<UserAccountInfo> getAllUserList()
        {
            StringBuilder sqlText = new StringBuilder("SELECT userId,username,password,administrator,staff,volunteer,firstName,lastName,addressLine1,addressLine2,");
            sqlText.Append("city,state,zipCode,homePhone,cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE activeData=1 ");
            sqlText.Append("ORDER BY lastName;");
            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            return retrieveUserAccountData(sqlCmd);
        }

        /// <summary>
        /// return account info for a id provided
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserAccountInfo getUserAccountInfoById(int id)
        {
            StringBuilder sqlText = new StringBuilder("SELECT userId,username,password,administrator,staff,volunteer,firstName,lastName,addressLine1,addressLine2,");
            sqlText.Append("city,state,zipCode,homePhone,cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE activeData=1 AND userId=");
            sqlText.Append(id.ToString());
            sqlText.Append(" ORDER BY lastName;");
            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            List<UserAccountInfo> userAccounts = retrieveUserAccountData(sqlCmd);


            if (userAccounts != null && userAccounts.Count > 0)
            {
                return userAccounts[0];  
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// return account info for username provided
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public UserAccountInfo getUserAccountInfoByUsername(string username)
        {
            StringBuilder sqlText = new StringBuilder("SELECT userId,username,password,administrator,staff,volunteer,firstName,lastName,addressLine1,addressLine2,");
            sqlText.Append("city,state,zipCode,homePhone,cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.UserAccount ");
            sqlText.Append("WHERE activeData=1 AND UPPER(username)=UPPER('");
            sqlText.Append(username);
            sqlText.Append("') ");
            sqlText.Append("ORDER BY lastName;");
            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            List<UserAccountInfo> userAccounts = retrieveUserAccountData(sqlCmd);


            if (userAccounts != null && userAccounts.Count > 0)
            {
                return userAccounts[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Returns data about a particular user
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        private List<UserAccountInfo> retrieveUserAccountData(SqlCommand sqlCmd)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader userAccountReader = null;
            userAccountReader = sqlCmd.ExecuteReader();
            UserAccountInfo userAccountInfo = null;
            List<UserAccountInfo> userAccounts = new List<UserAccountInfo>();
            ConversionUtility conversion = new ConversionUtility();

            while (userAccountReader.Read())
            {
                userAccountInfo = new UserAccountInfo();
                userAccountInfo.userId = (int)userAccountReader["userId"];
                userAccountInfo.username = userAccountReader["username"].ToString();
                userAccountInfo.password = userAccountReader["password"].ToString();
                userAccountInfo.administrator = (userAccountReader["administrator"].ToString().Equals("1"));
                userAccountInfo.staff = (userAccountReader["staff"].ToString().Equals("1"));
                userAccountInfo.volunteer = (userAccountReader["volunteer"].ToString().Equals("1"));
                userAccountInfo.firstName = userAccountReader["firstName"].ToString();
                userAccountInfo.lastName = userAccountReader["lastName"].ToString();
                userAccountInfo.addressLine1 = userAccountReader["addressLine1"].ToString();
                userAccountInfo.addressLine2 = userAccountReader["addressLine2"].ToString();
                userAccountInfo.city = userAccountReader["city"].ToString();
                userAccountInfo.state = userAccountReader["state"].ToString();
                userAccountInfo.zipCode = userAccountReader["zipCode"].ToString();
                userAccountInfo.homePhone = userAccountReader["homePhone"].ToString();
                userAccountInfo.cellPhone = userAccountReader["cellPhone"].ToString();
                userAccountInfo.businessPhone = userAccountReader["businessPhone"].ToString();
                userAccountInfo.emailAddress = userAccountReader["emailAddress"].ToString();
                userAccountInfo.biographyUrl = userAccountReader["biographyUrl"].ToString();
                userAccountInfo.modifiedBy = userAccountReader["modifiedBy"].ToString();
                userAccountInfo.modifiedTimestamp = conversion.stringToDateTime(userAccountReader["modifiedTimestamp"].ToString());

                userAccounts.Add(userAccountInfo);

            }
            closeConnection();
            return userAccounts;

        }

        /// <summary>
        /// insert new user account info into database
        /// </summary>
        /// <param name="userAccountInfo"></param>
        /// <param name="insertedByUsername"></param>
        /// <returns>true if user inserted into database</returns>
        public bool insertUserAccount(UserAccountInfo userAccountInfo, string insertedByUsername)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;
            ConversionUtility conversion = new ConversionUtility();

            try
            {
                StringBuilder sqlText = new StringBuilder("INSERT INTO UserAccount (username,password,administrator,staff,volunteer,firstName,lastName,");
                sqlText.Append("addressLine1,addressLine2,city,state,zipCode,homePhone,");
                sqlText.Append("cellPhone,businessPhone,emailAddress,biographyUrl,modifiedBy,modifiedTimestamp,activeData)");
                sqlText.Append(String.Format("VALUES ('{0}','{1}',{2},{3},{4},'{5}','{6}',",userAccountInfo.username, userAccountInfo.password, conversion.booleanToInt(userAccountInfo.administrator), conversion.booleanToInt(userAccountInfo.staff), conversion.booleanToInt(userAccountInfo.volunteer), userAccountInfo.firstName, userAccountInfo.lastName));
                sqlText.Append(String.Format("'{0}','{1}','{2}','{3}','{4}','{5}',",userAccountInfo.addressLine1,userAccountInfo.addressLine2,userAccountInfo.city,userAccountInfo.state,userAccountInfo.zipCode,userAccountInfo.homePhone));
                sqlText.Append(String.Format("'{0}','{1}','{2}','{3}','{4}','{5}',{6});", userAccountInfo.cellPhone, userAccountInfo.businessPhone, userAccountInfo.emailAddress, userAccountInfo.biographyUrl, insertedByUsername, DateTime.Now.ToString(), 1));

               // string sql = sqlText.Replace(@"@",@"\@").ToString();

                SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to insert.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

        /// <summary>
        /// update existing useraccount info in database
        /// </summary>
        /// <param name="userAccountInfo"></param>
        /// <param name="modifiedByUsername"></param>
        /// <returns>true if database updated</returns>
        public bool updateUserAccount(UserAccountInfo userAccountInfo, string modifiedByUsername)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                ConversionUtility conversion = new ConversionUtility();
                StringBuilder sqlText = new StringBuilder("UPDATE UserAccount SET ");
                sqlText.Append(String.Format("username='{0}', password='{1}', administrator={2}, staff={3}, volunteer={4}, firstName='{5}', lastName='{6}', ", userAccountInfo.username, userAccountInfo.password, conversion.booleanToInt(userAccountInfo.administrator), conversion.booleanToInt(userAccountInfo.staff), conversion.booleanToInt(userAccountInfo.volunteer), userAccountInfo.firstName, userAccountInfo.lastName));
                sqlText.Append(String.Format("addressLine1='{0}', addressLine2='{1}', city='{2}', state='{3}', zipCode={4}, homePhone='{5}', ", userAccountInfo.addressLine1,userAccountInfo.addressLine2,userAccountInfo.city,userAccountInfo.state,userAccountInfo.zipCode,userAccountInfo.homePhone));
                sqlText.Append(String.Format("cellPhone='{0}', businessPhone='{1}', emailAddress='{2}', biographyUrl='{3}', modifiedBy='{4}', modifiedTimestamp='{5}' ",userAccountInfo.cellPhone,userAccountInfo.businessPhone,userAccountInfo.emailAddress,userAccountInfo.biographyUrl,modifiedByUsername, DateTime.Now));
                sqlText.Append(String.Format("WHERE userId={0};", userAccountInfo.userId));

                SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);

        }

        /// <summary>
        /// delete existing user account record from database
        /// Note: A soft delete will be used so record will exist in database but will not 
        /// be returned with any query
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="deletedByUsername"></param>
        /// <returns>true if row successfully deleted from database</returns>
        public bool deleteUserAccount(int userId, string deletedByUsername)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string sqlText = String.Format("UPDATE UserAccount SET activeData=0, modifiedBy='{0}', modifiedTimestamp='{1}' WHERE retreatId={2};", deletedByUsername, DateTime.Now, userId);
                SqlCommand sqlCmd = new SqlCommand(sqlText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to delete user account.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }
    }
}